# -*-coding:UTF-8-*-
# encoding:gbk
from asyncio import ensure_future
import requests
from bs4 import BeautifulSoup
import sys
import xlwt
import json
import ijson
import re
import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root',
                     passwd='root', db='shengjing', charset='utf8')
cursor = db.cursor()

# 全局定义一个data变量,用来存储每大章的数据
data = {}

# 定义全局存放地址
file_url = "D:/Desktop/note/python学习/5th day/json_sjsy"

# 定义全局需爬取的网络地址
web_url = "http://shengjing.55cha.com"


def request(url):
    # print(url)
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko Core/1.70.3877.400 '
                      'QQBrowser/10.8.4506.400',
    }
    try:
        response = requests.get(url=url, headers=headers)
        # print(response.status_code)
        if response.status_code == 200:
            return response.text
    except requests.RequestException:
        return None


def page_save():
    # print(page)
    url = str(web_url)
    html = request(url)
    soup = BeautifulSoup(html, 'lxml')
    # pre = re.compile('>(.*?)<')

    # # 获取长度
    # for span in soup.find_all('ul', class_='l3'):
    #     page_row = span.find_all('li')
    # 获取值
    page_row2 = []
    for span in soup.find_all('ul', class_='l3'):
        for i in span.find_all('li'):
            # print(i)
            # print(type(i))
            i = str(i)
            a = re.sub(r'<[^>]+>', '', i)
            # a = i.find('a').string(a)
            page_row2.append(a)
    # 创建JSON文件,并写入数据

    data["menu"] = page_row2,

    # return len(page_row)


def save():

    # print(data)
    global data
    file_name = str(file_url) + '.json'

    for i in data["menu"][0]:
        print(i)
        try:
            sql = "INSERT INTO `menu` (`id`, `name`)" + " VALUES (NULL,'%s')" % (i)
            print(sql)

            cursor.execute(sql)
            db.commit()
        except Exception as e:
            db.rollback()
            print(e)

    # # 存储方法
    # with open(file_name, 'w', encoding='utf-8') as file_obj:
    #     '''写入json文件'''
    #     file_obj.write(json.dumps(data, indent=4, ensure_ascii=False))

    data = {}


if __name__ == '__main__':

    page_save()
    save()
    
    # 关闭光标对象
    cursor.close()
    # 关闭数据库连接
    db.close()
